package com.sj.purchase.modules.cost.repository.stock;

import com.sj.purchase.modules.cost.domain.stock.SkuStock;
import com.sj.purchase.modules.cost.domain.stock.SkuStockId;
import com.sj.purchase.modules.cost.domain.WarehouseEnum;
import com.sj.purchase.modules.cost.views.VSkuStock;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * Created by yangrd on 2017/7/19.
 */
public interface SkuStockRepository extends CrudRepository<SkuStock, SkuStockId> {

    @Query("select o.id.skuNo from SkuStock o where o.id.createDate =?1 and o.id.skuNo like %?2% and o.num!=0 group by o.id.skuNo")
    Page<String> listSkuNoByDayAndSkuLike(Date day, String skuNo, Pageable pageable);

    @Query("select new com.sj.purchase.modules.cost.views.VSkuStock(t.id.skuNo,sum (t.num))from SkuStock  t where t.id.createDate = ?1 group by t.id.skuNo")
    List<VSkuStock> findBeginStock(Date day);

    /**
     * 查询库存日期,查找未执行同步任务的日期
     */
    @Query("SELECT s.id.createDate FROM StockContrast s WHERE s.id.createDate BETWEEN ?1 AND ?2 GROUP BY s.id.createDate")
    List<Date> findByCreateTime(Date start,Date end);

    /**
     * 获取期初库存 返回sku,剩余库存
     */
    @Query("select o.id.skuNo ,sum (o.num) from SkuStock o where o.id.createDate =?1 and o.id.skuNo in (?2) group by o.id.skuNo")
    List<Object[]> findBeginStock(Date day, List<String> skuNOs);



    //获取 sku对应库存信息
    @Query("select a.id.skuNo,a.id.warehouse,sum(a.num) from SkuStock a where a.id.createDate = ?1 and a.id.skuNo in (?2) group by a.id.skuNo,a.id.warehouse")
    List<Object[]> findSkuStockGroupBySkuAndWarehouse(Date day, Collection<String> skuNos);


    //--算期末库存时使用

    //获取期初sku与入库sku的并集
    @Query(value = "" +
            "SELECT a.sku_no FROM (SELECT " +
            "s.sku_no " +
            "FROM " +
            " sku_stock s  " +
            "where s.create_date = ?2 and s.num !=0 " +
            "UNION  " +
            " SELECT " +
            "  i.sku_no " +
            "  FROM " +
            "  t_in_order_details i " +
            "  LEFT JOIN t_in_order o on o.id = i.order_id " +
            "  WHERE " +
            "  o.save_date_day = ?2 "+
            ") a " +
            "where a.sku_no like ?1 ORDER BY ?#{#pageable}", countQuery = "" +
            "SELECT COUNT(*) FROM (SELECT " +
            "s.sku_no " +
            "FROM " +
            " sku_stock s  " +
            "where s.create_date = ?2 and s.num !=0 " +
            "UNION  " +
            " SELECT " +
            "  i.sku_no " +
            "  FROM " +
            "  t_in_order_details i " +
            "  LEFT JOIN t_in_order o on o.id = i.order_id " +
            "  WHERE " +
            "  o.save_date_day = ?2 "+
            ") a " +
            "where a.sku_no like ?1 ", nativeQuery = true)
    Page<String> findBaseStockSku(String likeSku, Date day, Pageable pageable);


    /**
     * 获取期初sku与入库sku的并集
     *
     * @param start
     * @param end
     * @param warehouse 此处原本使用枚举(WarehouseEnum) 本地sql一直无法识别所以改为枚举对应的名字 使用时需要注意 WarehouseEnum.name()
     * @param pageable
     * @return
     * @see WarehouseEnum
     */
    @Query(value = "SELECT " +
            " a.sku_no  " +
            "FROM " +
            " ( " +
            "SELECT  " +
            " s.sku_no  " +
            "FROM " +
            " sku_stock s  " +
            "WHERE " +
            " s.warehouse = ?3  " +
            " AND s.create_date BETWEEN  ?1 AND ?2 " +
            " AND s.num>0 "+
            "  " +
            " UNION " +
            " " +
            "SELECT  " +
            " i.sku_no  " +
            "FROM " +
            " t_in_order o " +
            " INNER JOIN t_in_order_details i ON o.id = i.order_id " +
            "WHERE " +
            " o.warehouse_enum = ?3  " +
            " AND o.create_date BETWEEN ?1 AND ?2 " +
            " AND i.sku_no is not NULL" +
            " UNION " +
            "  SELECT " +
            "    i2.sku_no " +
            "  FROM " +
            "    t_out_order o2 " +
            "  INNER JOIN t_out_order_details i2 ON o2.id = i2.order_id " +
            "  WHERE " +
            "    o2.warehouse_enum = ?3 " +
            "  AND o2.create_date BETWEEN ?1 AND ?2 " +
            "  AND i2.sku_no IS NOT NULL"+
            " ) a " +
            " ORDER BY a.sku_no  /* #pageable */", countQuery = "SELECT " +
            " count(*) " +
            "FROM " +
            " ( " +
            "SELECT  " +
            " s.sku_no  " +
            "FROM " +
            " sku_stock s  " +
            "WHERE " +
            " s.warehouse = ?3  " +
            " AND s.create_date BETWEEN ?1 AND ?2  " +
            " AND s.num>0 "+
            "  " +
            " UNION " +
            "  " +
            "SELECT  " +
            " i.sku_no  " +
            "FROM " +
            " t_in_order o " +
            " INNER JOIN t_in_order_details i ON o.id = i.order_id " +
            "WHERE " +
            " o.warehouse_enum = ?3  " +
            " AND o.create_date BETWEEN ?1 AND ?2  " +
            " AND i.sku_no is not NULL" +
            " UNION " +
            "  SELECT " +
            "    i2.sku_no " +
            "  FROM " +
            "    t_out_order o2 " +
            "  INNER JOIN t_out_order_details i2 ON o2.id = i2.order_id " +
            "  WHERE " +
            "    o2.warehouse_enum = ?3 " +
            "  AND o2.create_date BETWEEN ?1 AND ?2 " +
            "  AND i2.sku_no IS NOT NULL"+
            " ) a  ", nativeQuery = true)
    Page<String> findBaseStockSku(Date start, Date end, String warehouse, Pageable pageable);

    @Query("select a.id.skuNo, a.num from SkuStock a where a.id.warehouse = ?1 and  a.id.createDate = ?2 and a.id.skuNo in ?3")
    List<Object[]> findSkuAndNum(WarehouseEnum warehouseEnum, Date day, List<String> skuNos);
}
